﻿CREATE TRIGGER [dbo].[tr_Registry]
ON [dbo].[t_Registry]
FOR INSERT,UPDATE,DELETE
AS

DECLARE @RC int SET @RC = @@ROWCOUNT
IF @RC > 1 BEGIN
	RAISERROR('Multiple updates of Registry table is not allowed.',16,1)
	RETURN
END

IF @RC = 0 RETURN

IF EXISTS(SELECT * FROM inserted WHERE Type IS NULL) BEGIN
	RAISERROR('Type column must be provided.',16,1)
	RETURN
END


DECLARE @Name sysname,@SQL varchar(8000),@FuncName sysname,@Prefix sysname SET @Prefix = 'Registry_'
IF NOT EXISTS(SELECT * FROM inserted)BEGIN

	SELECT @Name = @Prefix+Name FROM deleted
	EXEC('IF dbo.ObjectExists(N'''+@Name+''')=1 DROP FUNCTION '+@Name)
	RETURN
END

IF UPDATE(Name) BEGIN
	DECLARE @Type sysname
	SELECT @Type = t.name FROM sys.types t INNER JOIN inserted i ON i.Type = t.user_type_id
	SELECT @Name = Name,@FuncName = @Prefix+Name FROM inserted
	EXEC('IF dbo.ObjectExists(N'''+@FuncName+''')=1 DROP FUNCTION '+@FuncName)
	SET @SQL = '
	CREATE FUNCTION '+@Prefix+@Name+'()
	RETURNS '+@Type+' BEGIN RETURN CONVERT( '+@Type+', dbo.RegistryValue('''+@Name+'''))END
	'
	PRINT @SQL
	EXEC (@SQL)
	EXEC('PRINT dbo.'+@Prefix+@Name+'()')
END
--INSERT INTO t_Registry(Name,Value,Description,Type)
--VALUES('BookValuePullPeriod',10,'Days old before pull another value',56)
----registry_BookValuePullPeriod
